package db

import (
	"fmt"
	"strings"

	"git.sr.ht/~mil/mobroute/src/utilfuncs"
)

// LoadTransfers load the transfers from the underlying _vtransferstbl (and
// eventually _vtransfersgen) views.
//
// Note - IntArraysSortedFilter logic makes sures pairs are not duplicate e.g.
// [[1 3] [3 1] [5 1]] is reduced to [[1 3] [5 1]]
func (c *DBConnection) LoadTransfers(
	minTransferSeconds uint,
	maxTransferSeconds uint,
	walkspeedKmHr float64,
	transfersTbl,
	transfersGen bool,
) (*[]Transfer, error) {
	var (
		r             []Transfer
		sourcePairs   = utilfuncs.IntArraysSortedFilter(utilfuncs.Pairs(c.QuerySources))
		transferTypes = func() []string {
			types := []string{}
			if transfersTbl {
				types = append(types, "t")
			}
			if transfersGen || len(c.QuerySources) > 1 {
				types = append(types, "g")
			}
			return types
		}()
		query = fmt.Sprintf(
			`select
				*, 
				-- In order of priority:
				-- A) use min_transfer_time (for t transfers this will be non null)
				-- B) use seconds based on generated dist_km (dist_km calculated for both t & g transfers)
				-- C) (A/B nil || < param), then use minimum transfer secs from params
				 max(coalesce(min_transfer_time, cast(dist_km / ? * 60 * 60 as uint), ?), ?) as transfer_time
			 from
			 	 (select * from _cvtransfersgen union all select * from _vtransferstbl) g
			 where
			 	 (%s) and (transfer_time < ?) and transfer_category in (%s)
			`,
			strings.Join(utilfuncs.ArrayWithValue(len(sourcePairs), "(source = ? and source_pair = ?)"), " or "),
			strings.Join(utilfuncs.ArrayWithValue(len(transferTypes), "?"), " , "),
		)
		params = utilfuncs.Flatten([][]any{
			[]any{walkspeedKmHr, minTransferSeconds, minTransferSeconds},
			utilfuncs.Anyify(utilfuncs.Flatten(sourcePairs)),
			[]any{any(maxTransferSeconds)},
			utilfuncs.Anyify(transferTypes),
		})
	)

	if err := queryToSlice(c, query, params, &r); err != nil {
		return nil, err
	} else if len(transferTypes) == 1 && transferTypes[0] == "t" && len(r) == 0 {
		// E.g. rerun pulling generated transfers if transfers table empty
		return c.LoadTransfers(minTransferSeconds, maxTransferSeconds, walkspeedKmHr, false, true)
	} else {
		return &r, nil
	}
}
